package seismosurfer.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;

import seismosurfer.data.UserData;
import seismosurfer.data.constants.ParameterNames;
import seismosurfer.util.SeismoException;

import com.bbn.openmap.util.Debug;

/**
 * A DAO class that encapsulates all access to the
 * SEISMO_USER table.
 *
 */
public class UserDAO implements ParameterNames {

    protected static final String ERROR_ON_USER_INSERT = "Error during user registration.";

    protected static final String USER_FROM = " SELECT username, password, nickname, country, firstname, lastname, city "
            + " FROM user_view WHERE ";

    protected static final String INSERT_USER = " insert into seismo_user "
            + " (username, password, nickname, country, firstname, lastname, city, lddate) "
            + " values (?, ?, ?, ?, ?, ?, ?, default) ";

    protected static final String INSERT_USER_ROLE = " insert into user_role "
            + " (username, role) " + " values (?, ?) ";

    protected static final String FIND_CONSTRAINTS = "SELECT component_name FROM role_constraint WHERE role = ? ";

    protected static final String ROWS_COUNT = "SELECT numberOfRows FROM seismo_role WHERE role = ? ";

    protected static final String OPERATOR = " = ? ";
    
    /**
     * Default empty constructor.
     *
     */
    public UserDAO() {
    }
    
    /**
     * Gets the constraints for a given user role.
     * The constraints are menu and menu items names.
     * 
     * @param role the role which is assigned to a user
     * @return a list with the constraints
     */
    public List getConstraints(String role) {

        List result = new Vector();

        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = DB.prepare(FIND_CONSTRAINTS);
            stmt.setString(1, role);
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(rs.getString("COMPONENT_NAME"));
            }

            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }
    
    /**
     * Gets the maximum number of rows that
     * a user in a given role can retrieve.
     * 
     * @param role  the role, a user is assigned to
     * @return the maximum number of rows
     */
    public int getMaxRows(String role) {
        int maxRows = -1;

        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {            
            stmt = DB.prepare(ROWS_COUNT);
            stmt.setString(1, role);
            rs = stmt.executeQuery();

            if (rs.next()) {
                maxRows = rs.getInt("NUMBEROFROWS");
            }
            return maxRows;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }
    }
    
    /**
     * Insert a new user data in the db.
     * 
     * @param user the UserData of a user
     */
    public static void insert(UserData user) {

        PreparedStatement stmt = null;

        try {

            Debug.output("Inserting user..." + user.toString());

            stmt = DB.prepare(INSERT_USER);

            stmt.setString(1, user.getUserName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getNickName());
            stmt.setString(4, user.getCountry());
            stmt.setString(5, user.getFirstName());
            stmt.setString(6, user.getLastName());
            stmt.setString(7, user.getCity());

            stmt.executeUpdate();

            Debug.output("Inserted user..."
                    + findUser(USERNAME, user.getUserName()).toString());

        } catch (SQLException ex) {
            throw new SeismoException(UserDAO.ERROR_ON_USER_INSERT, ex);
        } finally {
            DB.cleanUp(stmt);
        }
    }
    
    /**
     * Insert a new user data in the db.
     * 
     * @param username  the name of the user
     * @param role  the role of the user
     */
    public static void insertUserRole(String username, String role) {
        PreparedStatement stmt = null;

        try {
            stmt = DB.prepare(INSERT_USER_ROLE);

            stmt.setString(1, username);
            stmt.setString(2, role);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            throw new SeismoException(UserDAO.ERROR_ON_USER_INSERT, ex);
        } finally {
            DB.cleanUp(stmt);
        }

    }
    
    /**
     * Loads a row from the database into a UserData object.
     * 
     * @param rs The resultset from which the row will be loaded.
     * @return A UserData object which contains the data of a row.
     * @throws SQLException
     */
    public static UserData load(ResultSet rs) throws SQLException {
        UserData user = new UserData();

        user.setUserName(rs.getString(1));
        user.setPassword(rs.getString(2));
        user.setNickName(rs.getString(3));
        user.setCountry(rs.getString(4));
        user.setFirstName(rs.getString(5));
        user.setLastName(rs.getString(6));
        user.setCity(rs.getString(7));

        return user;
    }
    
    /**
     * Retrieves a user`s data given a field and its value 
     * to identify her.
     * 
     * @param field One of the columns name of the SEISMO_USER table.
     * @param value the value of the database field
     * @return a UserData object which contains the user`s data
     */
    public static UserData findUser(String field, String value) {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        UserData user = null;

        try {

            stmt = DB.prepare(USER_FROM + field + OPERATOR);
            stmt.setString(1, value);

            rs = stmt.executeQuery();

            if (rs.next()) {
                user = load(rs);
            }

            return user;

        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }

}